package com.apes.fn.scm.retailOrder.service.tc.vipSurplus;

import com.alibaba.fastjson.JSONArray;
import com.apes.fn.scm.retailOrder.service.tc.commission.EmployeeCommissionDao;
import com.apes.framework.query.ApesDao;
import com.apes.framework.query.SqlService;
import com.apestech.framework.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * @Title: VipSurplusDao
 * @Description:
 * @author: ZhengZeXian
 * @create: 2021/8/27 9:51
 */
@Component
public class VipSurplusDao extends ApesDao {

    @Autowired
    private SqlService sqlService;
    @Autowired
    private EmployeeCommissionDao dao;

    /** 查询老系统零售单 循环收款*/
    public List<Map> queryOldVipSk(JSONObject param){

        checkNull(param,"DATE_START","DATE_END");

        String sql = "SELECT T.XSDDH AS RETAIL_ORDER_ID,\n" +
                "                           T.JZRQ AS SYS_RQ,\n" +
                "                           MX.SKFSID,\n" +
                "                           MX.SKFSMC,\n" +
                "                           MX.GSID,\n" +
                "                           MX.SSGSID,\n" +
                "                           MX.SKJE AS SYJE,\n" +
                "                           A.VIPID,\n" +
                "                           A.VIPXM,\n" +
                "                           A.SJHM,\n" +
                "                           A.CPHM,\n" +
                "                           A.SKRQ_SJ,\n" +
                "                           A.DJLXID,\n" +
                "                           '0' PRODUCT_ID,\n" +
                "                           '0001' VIP_ALLIANCE,\n" +
                "                           '0' CLASSIFY_ID,\n" +
                "                           0 DKLX,\n" +
                "                           SYSDATE XRRQ_SJ" +
                "                      FROM SAL_XSDD T, SAL_SKD A, SAL_SKDFSMX MX\n" +
                "                     WHERE T.XSDDH = A.SKDH\n" +
                "                       AND A.SKDH = MX.SKDH\n" +
                "                       AND MX.SKFSID IN ('15', '05')\n" +
                "                       AND T.JZRQ IS NOT NULL\n" +
                "                       AND MX.GSID != MX.SSGSID\n" +
                "                       AND T.JZRQ >= :DATE_START AND T.JZRQ < :DATE_END";
        return this.find(sql,param);
    }

    /** 查询老系统零售单 循环商品*/
    public List<Map> queryOldVipSp(JSONObject param){

        checkNull(param,"DATE_START","DATE_END");

        String sql = "SELECT A.XSDDH AS RETAIL_ORDER_ID,\n" +
                "                              A.JZRQ AS SYS_RQ,\n" +
                "                              C.SPID AS PRODUCT_ID,\n" +
                "                              C.SPMC AS PRODUCT_NAME,\n" +
                "                              C.PLID AS CLASSIFY_ID,\n" +
                "                              C.PLMC AS CLASSIFY_NAME,\n" +
                "                              SUBSTR(C.PLID,1,1) SUB_CLASS,\n" +
                "                              C.JLDW AS PRODUCT_UOM_ID,\n" +
                "                              C.YXSDJ DSJ,\n" +
                "                              CASE WHEN C.KHCBJ !=0 THEN C.KHCBJ\n" +
                "                              ELSE C.CBDJ*(1+C.SSBL) END CBJ,\n" +
                "                              C.XSDJ ,\n" +
                "                              B.DHQID,\n" +
                "                              B.DHQMC,\n" +
                "                              B.DHQDKFS AS DKLX,\n" +
                "                              NVL(B.ZKHBLX,0) HBLX,\n" +
                "                              B.ZKJE AS DKJE,\n" +
                "                              B.CXFSLB,\n" +
                "                              B.DHQYHJE,\n" +
                "                              B.DHQZKJE + B.DHQYHJE AS DHQDKJE,\n" +
                "                              B.DHQSYSL AS DHQSL,\n" +
                "                              A.VIPID,\n" +
                "                              A.VIPXM,\n" +
                "                              A.SJHM,\n" +
                "                              A.CPHM,\n" +
                "                              A.JZRQ AS SKRQ_SJ,\n" +
                "                              A.XSZZID GSID,\n" +
                "                              A.XSZZMC,\n" +
                "                              B.SSGSID,\n" +
                "                              '0001' VIP_ALLIANCE,\n" +
                "                              CASE WHEN A.YXSDDH IS NOT NULL THEN A.YXSDDH\n" +
                "                              ELSE '1' END YXSDDH,\n" +
                "                              A.DJLXID,\n" +
                "                              B.XSDDH||B.ZJXH ZKBID,\n" +
                "                              SYSDATE XRRQ_SJ" +
                "                         FROM SAL_XSDD A, SAL_XSDDZKPC B, SAL_XSDDMX C\n" +
                "                        WHERE A.XSDDH = C.XSDDH\n" +
                "                          AND C.XSDDH = B.XSDDH\n" +
                "                          AND C.XH = B.XMXH\n" +
                "                          AND A.JZRQ IS NOT NULL\n" +
                "                          AND A.XSZZID != B.SSGSID\n" +
                "                          AND A.JZRQ >= :DATE_START AND A.JZRQ < :DATE_END";
        return this.find(sql,param);
    }

    /** 查询新系统零售单 循环收款*/
    public List<Map> queryNewVipSk(JSONObject param){

        checkNull(param,"ID");

        String sql = "SELECT B.ID AS RETAIL_ORDER_ID,\n" +
                "                           B.DATE_DONE AS SYS_RQ,\n" +
                "                           A.PAYMENT_METHOD_TYPE AS SKFSID,\n" +
                "                           (SELECT NAME FROM mas_payment_method WHERE ID = A.PAYMENT_METHOD_TYPE) SKFSMC,\n" +
                "                           B.COMPANY_ID AS GSID,\n" +
                "                           D.COMPANY_ID AS SSGSID,\n" +
                "                           D.AMOUNT AS SYJE,\n" +
                "                           B.CUSTOMER_EXPAND_ID AS VIPID,\n" +
                "                           C.REALNAME AS VIPXM,\n" +
                "                           C.MOBILE AS SJHM,\n" +
                "                           C.CAR_LICENSE AS CPHM,\n" +
                "                           GET_DATE(B.DATE_DONE) SKRQ_SJ,\n" +
                "                           '0' PRODUCT_ID,\n" +
                "                           D.VIP_ALLIANCE VIP_ALLIANCE,\n" +
                "                           '0' CLASSIFY_ID,\n" +
                "                           B.ORDER_TYPE DJLXID,\n" +
                "                           0 DKLX,\n" +
                "                           SYSDATE XRRQ_SJ," +
                "                           D.DEPT_ID SSBMID , B.DEPT_ID BMID" +    // 零售单的部门为消费部门 收款方式明细表为所属部门
                "                      FROM RETAIL_ORDER_PAYMENT_METHOD A,\n" +
                "                           RETAIL_ORDER                B,\n" +
                "                           FN_CUSTOMER_EXPAND          C,\n" +
                "                           RETAIL_PAYMENT_METHOD_ITEM  D\n" +
                "                     WHERE A.RETAIL_ORDER_ID = B.ID\n" +
                "                       AND A.ID = D.RETAIL_PAYMENT_ID\n" +
                "                       AND B.CUSTOMER_EXPAND_ID = C.ID\n" +
                "                       AND B.STATE IN ('done', 'vouched')\n" +
                "                       AND B.COMPANY_ID != D.COMPANY_ID\n" +
                "                       AND A.PAYMENT_METHOD_TYPE = '05'\n" +
                "                       AND B.ID= :ID \n";
//                "                       AND B.DATE_DONE >= to_date('2021-08-19','yyyy-mm-dd') AND B.DATE_DONE < to_date('2021-09-01','yyyy-mm-dd')\n";
        return this.find(sql,param);
    }

    /** 查询新系统零售单 循环商品*/
    public List<Map> queryNewVipSp(JSONObject param){

        checkNull(param,"ID");

        String sql = "SELECT B.ID AS RETAIL_ORDER_ID,\n" +
                "                           B.DATE_DONE AS SYS_RQ,\n" +
                "                           E.PRODUCT_ID,\n" +
                "                           G.NAME PRODUCT_NAME,\n" +
                "                           G.CLASSIFY_ID,\n" +
                "                           (SELECT NAME FROM MAS_CLASSIFY WHERE ID = G.CLASSIFY_ID) CLASSIFY_NAME,\n" +
                "                           SUBSTR(G.CLASSIFY_ID,1,1) SUB_CLASS,\n" +
                "                           (SELECT T.NAME FROM MAS_PRODUCT_UOM T WHERE T.ID = G.PRODUCT_UOM_ID )PRODUCT_UOM_ID,\n" +
                "                           E.ORIGINAL_SALES_UNIT_PRICE DSJ,\n" +
                "                           CASE WHEN  E.REFER_TAX !=0 THEN E.REFER_TAX\n" +
                "                           ELSE E.PRICE_OUT_UNIT_IN_TAX END CBJ,\n" +
                "                           E.PRICE_UNIT XSDJ,\n" +
                "                           A.COUPON_PROFIT_AMOUNT DHQDKJE,\n" +
                "                           A.COUPON_NUMBER DHQID,\n" +
                "                           A.COUPON_RESOURCE DHQMC,\n" +
                "                           A.COUPON_USED_QTY DHQSL,\n" +
                "                           A.DISCOUNT_TYPE CXFSLB,\n" +
                "                           NVL(D.TYPE,0) HBLX,\n" +
                "                           D.AMOUNT DKJE,\n" +
                "                           B.COMPANY_ID AS GSID,\n" +
                "                           D.COMPANY_ID AS SSGSID,\n" +
                "                           B.CUSTOMER_EXPAND_ID AS VIPID,\n" +
                "                           C.REALNAME AS VIPXM,\n" +
                "                           C.MOBILE AS SJHM,\n" +
                "                           C.CAR_LICENSE AS CPHM,\n" +
                "                           GET_DATE(B.DATE_DONE) SKRQ_SJ,\n" +
                "                           D.VIP_ALLIANCE VIP_ALLIANCE,\n" +
                "                           A.COUPON_DISCOUNT_TYPE DKLX,\n" +
                "                            CASE WHEN  (SELECT t.sale_id FROM retail_return t WHERE t.id =  b.SOURCE ) IS NOT NULL THEN  (SELECT t.sale_id FROM retail_return t WHERE t.id =  b.SOURCE )\n" +
                "                           ELSE '1' END SOURCE,\n" +
                "                           B.ORDER_TYPE DJLXID,\n" +
                "                           D.ID ZKBID,\n" +
                "                           SYSDATE XRRQ_SJ," +
                "                           D.DEPT_ID SSBMID , B.DEPT_ID BMID," + // 零售单的部门为消费部门 折扣明细表为所属部门
                "                           (SELECT T.AMT FROM ACC_TAX T WHERE T.ID = G.TAX_ID) /100 TAX_AMT" +
                "                      FROM RETAIL_ORDER_DISCOUNT       A,\n" +
                "                           RETAIL_ORDER                B,\n" +
                "                           FN_CUSTOMER_EXPAND          C,\n" +
                "                           RETAIL_ORDER_DISCOUNT_ITEM  D,\n" +
                "                           RETAIL_ORDER_ITEM           E,\n" +
                "                           MAS_PRODUCT                 G\n" +
                "                     WHERE A.RETAIL_ORDER_ID = B.ID\n" +
                "                       AND A.ID = D.RETAIL_DISCOUNT_ID\n" +
                "                       AND B.CUSTOMER_EXPAND_ID = C.ID\n" +
                "                       AND B.ID = E.SALE_ID\n" +
                "                       AND E.ID = A.RETAIL_ITEM_ID\n" +
                "                       AND E.PRODUCT_ID = G.ID\n" +
                "                       AND B.STATE IN ('done', 'vouched')\n" +
                "                       AND B.COMPANY_ID != D.COMPANY_ID\n" +
                "                       AND B.ID= :ID\n";
//                "                       AND B.DATE_DONE >= to_date('2021-08-19','yyyy-mm-dd ') AND B.DATE_DONE < to_date('2021-09-01','yyyy-mm-dd ')\n";
        return this.find(sql,param);
    }

    /**查询老系统零售单循环收款 公司所属接入商*/
    public JSONArray queryOldVipGsAccess(JSONObject param){
        checkNull(param,"GSID");

        String sql = "SELECT ACCESS_ID GS_ACCESS FROM S_ACCESS_COMPANY T WHERE T.COMPANY_ID = :GSID";
        return toJSONArray(this.find(sql, param));
    }

    /**查询老系统零售单循环收款 所属公司所属接入商*/
    public JSONArray queryOldVipSsgsAccess(JSONObject param){
        checkNull(param,"SSGSID");

        String sql = "SELECT ACCESS_ID SSGS_ACCESS FROM S_ACCESS_COMPANY T WHERE T.COMPANY_ID = :SSGSID";
        return toJSONArray(this.find(sql, param));
    }

    /**查询老系统零售单循环商品 原抵扣类型*/
    public JSONArray queryOldVipSpYdklx(JSONObject param){
        checkNull(param,"PRODUCT_ID","YXSDDH");

        String sql = "SELECT DISTINCT(T.DKLX) YDKLX FROM VIP_SURPLUS_SP T WHERE T.PRODUCT_ID = :PRODUCT_ID AND T.RETAIL_ORDER_ID = :YXSDDH";
        return toJSONArray(this.find(sql, param));
    }

    /**查询新系统零售单循环商品 原抵扣类型*/
    public JSONArray queryNewVipSpYdklx(JSONObject param){
        checkNull(param,"PRODUCT_ID","SOURCE");

        String sql = "SELECT DISTINCT(T.DKLX) YDKLX FROM VIP_SURPLUS_SP T WHERE T.PRODUCT_ID = :PRODUCT_ID AND T.RETAIL_ORDER_ID = :SOURCE";
        return toJSONArray(this.find(sql, param));
    }

    /**会员联盟结算明细-收款 判断是否存在 */
    public JSONArray existVipSurplusSk(JSONObject param){
        checkNull(param,"SKFSID","RETAIL_ORDER_ID","SSGSID");

        String sql = "SELECT COUNT(1) CZBJ FROM VIP_SURPLUS_SK T WHERE T.SKFSID = :SKFSID AND T.RETAIL_ORDER_ID = :RETAIL_ORDER_ID AND SSGSID = :SSGSID";
        return toJSONArray(this.find(sql, param));
    }

    /**会员联盟结算明细-商品 判断是否存在 */
    public JSONArray existVipSurplusSp(JSONObject param){
        checkNull(param,"PRODUCT_ID","RETAIL_ORDER_ID","ZKBID");

        String sql = "SELECT COUNT(1) CZBJ FROM VIP_SURPLUS_SP T WHERE T.PRODUCT_ID = :PRODUCT_ID AND T.RETAIL_ORDER_ID = :RETAIL_ORDER_ID AND T.ZKBID= :ZKBID AND T.HBLX=:HBLX";
        return toJSONArray(this.find(sql, param));
    }

    /** 插入 会员联盟结算明细-收款*/
    public void insertVipSurplusSk(JSONObject param) {
        String sSQL = "INSERT INTO VIP_SURPLUS_SK (RETAIL_ORDER_ID, SKFSID, SKFSMC, GSID, SSGSID, SYJE, JSJE, VIPID, VIPXM, SJHM, CPHM, SKRQ_SJ, MDID,XRRQ_SJ,BMID,SSBMID,VIP_ALLIANCE)\n" +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?,?,?,?,?)";

        dao.executeInsert(sSQL, param);
    }

    /** 插入 会员联盟结算明细-商品*/
    public void insertVipSurplusSp(JSONObject param) {
        String sSQL = "INSERT INTO VIP_SURPLUS_SP\n" +
                "  (RETAIL_ORDER_ID, GSID, SSGSID, PRODUCT_ID, PRODUCT_NAME, PRODUCT_UOM_ID, XSDJ, DKJE, DKJSJE, FXJE, FXJSJE, DHQSL,\n" +
                "   DHQID, DHQMC, DHQDKJE, DHQJSJE, CBJ, DSJ, VIPID, VIPXM, SJHM, CPHM, SKRQ_SJ, ZKBID, DKLX, MDID, BBDKJE, BBJSJE, HBLX,XRRQ_SJ,BMID,SSBMID,HBYJSE,BBHBYJSE,VIP_ALLIANCE)" +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?,?,?)";

        dao.executeInsert(sSQL, param);
    }

    /***
     * 用于验证参数不能为空
     */
    private void checkNull(JSONObject param, String... items){
        for (String field : items) {
            if (param.get(field) == null) throw new RuntimeException("参数：" + field + "不能为空");
        }
    }
}
